﻿using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using Spire.Doc;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
using System.Xml.Linq;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            List<SelectListItem> supplierList = new List<SelectListItem>();
            supplierList.Add(new SelectListItem { Value = "", Text = "请选择" });
            supplierList.Add(new SelectListItem { Value = "YZ001", Text = "供应商" });
            supplierList.Add(new SelectListItem { Value = "YZ001", Text = "供应商" });
            supplierList.Add(new SelectListItem { Value = "YZ001", Text = "供应商" });
            supplierList.Add(new SelectListItem { Value = "YZ001", Text = "供应商" });

            for (int i = 0; i < supplierList.Count; i++)  //外循环是循环的次数
            {
                for (int j = supplierList.Count - 1; j > i; j--)  //内循环是 外循环一次比较的次数
                {

                    if (supplierList[i].Value == supplierList[j].Value)
                    {
                        supplierList.RemoveAt(j);
                    }

                }
            }



            DataTable dataTable = new DataTable("Student");
            dataTable.Columns.Add("syscode", typeof(String));
            dataTable.Columns.Add("sysname", typeof(String));
            dataTable.Rows.Add(new String[] { "MF15", "请购单接口" });
            dataTable.Rows.Add(new String[] { "MF16", "采购订单接口" });
            dataTable.Rows.Add(new String[] { "MF18", "采购入库接口" });
            dataTable.Rows.Add(new String[] { "MF11", "付款单接口" });
            dataTable.Rows.Add(new String[] { "MF10", "应付单接口" });
            this.comboBox1.DataSource = dataTable;//绑定
            this.comboBox1.DisplayMember = dataTable.Columns[1].ColumnName;//显示的文本
            this.comboBox1.ValueMember = dataTable.Columns[0].ColumnName;//对应的值

            //DateSubstract();
        }




        private void DateSubstract()
        {
            int hours = 0;
            int daysTemp = 0;
            string strDateTemp = "";
            DateTime dateNow = DateTime.Now;

            string[] arr = { "2016-12-8", "2016-11-9", "2016-12-11", "2017-12-23" };

            for (int i = 0; i < 4; i++)
            {
                DateTime tempData = DateTime.Parse(arr[i]);
                hours = Math.Abs(tempData.Subtract(dateNow).Hours);


                if (daysTemp != 0)
                {
                    if (daysTemp > hours)
                    {
                        daysTemp = hours;
                        strDateTemp = tempData.ToString();
                    }
                }
                else
                {
                    daysTemp = hours;
                    strDateTemp = tempData.ToString();
                }
            }

            MessageBox.Show(strDateTemp);
        }



        private void button1_Click(object sender, EventArgs e)
        {


            if (this.textBox1.Text.Trim().Length < 100)
            {
                MessageBox.Show("请输入内容！");
                return;
            }

            string strXml = this.textBox1.Text;
            //ServiceReference1.PubServivePortTypeClient doSendXml = new ServiceReference1.PubServivePortTypeClient();

            ServiceReference1.PubServivePortTypeClient ncService
                = new ServiceReference1.PubServivePortTypeClient("IPubServiveSOAP11port_http", "http://192.168.27.202:655/uapws/service/pubws");//后面的参数是url

            string result = ncService.DoSendXML(this.comboBox1.SelectedValue.ToString(), strXml);

            this.textBox2.Text = result;

            //ServiceReference2.PMPServiceSoapClient pmp = new ServiceReference2.PMPServiceSoapClient();
            //string result = pmp.SendSupplierInfo(this.textBox1.Text.Trim());
            //string result = pmp.SendContractPaymentConfirmInfo(this.textBox1.Text.Trim());

            // this.textBox2.Text = result;

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //string strKey = "BusinessUnit_" + System.Guid.NewGuid().ToString();
            //ICacheProvider RidesCache = CacheFactory.CreateCacheProvider("PSMRedis");

            //if (RidesCache.Contains(strKey))
            //{
            //    string strValue = RidesCache.Get<string>(strKey);

            //    MessageBox.Show(strValue);
            //}
            //else
            //{
            //    Hashtable ht=new Hashtable();
            //    ht.Add("Age", "32");
            //    ht.Add("Name", "Joe");
            //    RidesCache.Insert<Hashtable>(strKey, ht);

            //    Hashtable HaskKey = RidesCache.Get<Hashtable>(strKey);
            //    foreach (string key in HaskKey)
            //    {
            //        Console.WriteLine("HashID--Key:{0}", key);
            //    }

            //    //RidesCache.Insert<string>(strKey, "HelloRedis!!!");
            //}
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            this.textBox1.Text = "";
            this.textBox2.Text = "";
        }

        private void button3_Click(object sender, EventArgs e)
        {

            string strXml = this.textBox1.Text;

            //ServiceReference2.PMPServiceSoapClient pmpservice = new ServiceReference2.PMPServiceSoapClient();
            //string result = pmpservice.SendSupplierInfo(strXml);

            ServiceReference3.PMPServiceSoapClient pmpservice = new ServiceReference3.PMPServiceSoapClient();
            string result = pmpservice.SendSupplierInfo(strXml);

            //this.textBox2.Text = strXml.Substring(0,32767);

            this.textBox2.Text = result;
        }

        private void btnSuiYing_Click(object sender, EventArgs e)
        {
            Document doc = new Document();
            doc.LoadFromFile(@"d:\1111.docx");
            //设图片水印
            PictureWatermark picture = new PictureWatermark();
            picture.Picture = System.Drawing.Image.FromFile(@"d:\404bg.jpg");
            picture.Scaling = 80;
            doc.Watermark = picture;
            doc.SaveToFile(@"d:\1111222.docx");

            MessageBox.Show("生成水印成功");
            ////设置文本水印
            //TextWatermark txtWatermark = new TextWatermark();
            //txtWatermark.Text = "Microsoft";
            //txtWatermark.FontSize = 90;
            //txtWatermark.Layout = WatermarkLayout.Diagonal;
            //doc.Watermark = txtWatermark;
            //doc.SaveToFile("水印.doc");

        }

        private void button4_Click(object sender, EventArgs e)
        {
            string strKey = this.txtKey.Text.Trim();
            string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2048;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";
            connString += "DBQ=" + strKey;
            OdbcConnection conn = new OdbcConnection(connString);
            OdbcCommand cmd = new OdbcCommand();
            cmd.Connection = conn;

            string sql1 = "";
            string sql2 = "";

            if (this.radioButton1.Checked)
            {
                sql1 = "select * from [sheet-0$]";
            }
            else
            {
                sql1 = "select * from [sheet-1$]";
                sql2 = "select * from [currentDB$]";
            }

            cmd.CommandText = sql1;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);

            cmd.CommandText = sql2;
            OdbcDataAdapter da2 = new OdbcDataAdapter(cmd);
            DataSet ds2 = new DataSet();
            da2.Fill(ds2);


            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                if (this.radioButton1.Checked)
                {
                    sb.Append(" insert into dbo.BusinessUnitInformation");
                    sb.Append(" (PSMProjectCode,BusinessUnitCode,BusinessUnitName,FatherBusinessUnitCode,FatherBusinessUnitName,Rate,CreateUser,");
                    sb.Append("  CreateDate,LastUpdateUser,LastUpdateDate)");
                    sb.Append("  values (");
                    sb.Append("  '" + ds.Tables[0].Rows[i]["PSMProjectCode"].ToString() + "','" + ds.Tables[0].Rows[i]["BusinessUnitCode"].ToString() + "',");
                    sb.Append("  '" + ds.Tables[0].Rows[i]["BusinessUnitName"].ToString() + "','" + ds.Tables[0].Rows[i]["FatherBusinessUnitCode"].ToString() + "',");
                    sb.Append("  '" + ds.Tables[0].Rows[i]["FatherBusinessUnitName"].ToString() + "','" + ds.Tables[0].Rows[i]["Rate"].ToString() + "',");
                    sb.Append("  'System','" + DateTime.Now + "','System','" + DateTime.Now + "'");
                    sb.Append("  )").Append(Environment.NewLine);
                }
                else
                {
                    var row = ds2.Tables[0].Select("NcPKey='" + ds.Tables[0].Rows[i]["NcPKey"].ToString() + "'");
                    if (row.Count() > 0)
                    {
                        sb2.Append(" update dbo.Supplier");
                        sb2.Append(" set SupplierName='" + ds.Tables[0].Rows[i]["SupplierName"].ToString() + "',");
                        sb2.Append(" SupplierType='" + ds.Tables[0].Rows[i]["SupplierType"].ToString() + "',");
                        sb2.Append(" SupplierCode='" + ds.Tables[0].Rows[i]["SupplierCode"].ToString() + "',");
                        sb2.Append(" BankAccount='" + ds.Tables[0].Rows[i]["BankAccount"].ToString() + "',");
                        sb2.Append(" RegionAccount='" + ds.Tables[0].Rows[i]["RegionAccount"].ToString() + "',");
                        sb2.Append(" BankId='" + ds.Tables[0].Rows[i]["BankId"].ToString() + "',");
                        sb2.Append(" ContractUser='" + ds.Tables[0].Rows[i]["ContractUser"].ToString() + "',");
                        sb2.Append(" ContractTel='" + ds.Tables[0].Rows[i]["ContractTel"].ToString() + "',");
                        sb2.Append(" Enablestate='" + ds.Tables[0].Rows[i]["Enablestate"].ToString() + "',");
                        sb2.Append(" TaxRate=" + ds.Tables[0].Rows[i]["TaxRate"].ToString() + ",");
                        sb2.Append(" OperatorUser='" + ds.Tables[0].Rows[i]["OperatorUser"].ToString() + "',");
                        sb2.Append(" OperatorDate='" + ds.Tables[0].Rows[i]["OperatorDate"].ToString() + "'");
                        sb2.Append(" where NcPKey='" + ds.Tables[0].Rows[i]["NcPKey"].ToString() + "'");
                        sb2.Append("  ").Append(Environment.NewLine);
                    }
                    else
                    {
                        sb.Append(" insert into dbo.Supplier");
                        sb.Append(" (NcPKey,SupplierName,SupplierType,SupplierCode,BankAccount,RegionAccount,BankId,ContractUser,ContractTel,Enablestate,TaxRate,");
                        sb.Append("  OperatorUser,OperatorDate)");
                        sb.Append("  values (");
                        sb.Append("  '" + ds.Tables[0].Rows[i]["NcPKey"].ToString() + "','" + ds.Tables[0].Rows[i]["SupplierName"].ToString() + "',");
                        sb.Append("  '" + ds.Tables[0].Rows[i]["SupplierType"].ToString().PadLeft(2, '0') + "','" + ds.Tables[0].Rows[i]["SupplierCode"].ToString() + "',");
                        sb.Append("  '" + ds.Tables[0].Rows[i]["BankAccount"].ToString() + "','" + ds.Tables[0].Rows[i]["RegionAccount"].ToString() + "',");
                        sb.Append("  '" + ds.Tables[0].Rows[i]["BankId"].ToString() + "','" + ds.Tables[0].Rows[i]["ContractUser"].ToString() + "',");
                        sb.Append("  '" + ds.Tables[0].Rows[i]["ContractTel"].ToString() + "','" + ds.Tables[0].Rows[i]["Enablestate"].ToString() + "',");
                        sb.Append("  '0.00',");
                        sb.Append("  'System','" + DateTime.Now + "'");
                        sb.Append("  )").Append(Environment.NewLine);
                    }
                }
            }

            this.textBox1.Text = sb2.ToString();
            this.textBox2.Text = sb.ToString();
        }

        /// <summary>
        /// 更新处理状态
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button5_Click(object sender, EventArgs e)
        {
            //同步指定供应商从PMP至PSM
            //string url = "http://psmapi2.52mf.cn/api/SynchronizationSupplier/SynchronizationSingleSupplier";
            //参数{"NcPKey":"1001A110000000002CVQ"}

            //ApiName:UpdateStatusOfRentPaymentRequestDetail
            //ApiName:UpdateStatusOfRentPaymentRequest
            //ApiName:UpdateStatusOfContractIntention  
            //ApiName:UpdateStatusOfNCLog
            //ApiName:UpdateUnitOfRentOfRentPaymentRequestDetail
            var param = new UpdateStatusModel
            {
                id = 42,
                status = 1,
                values = "A|B|C|D|E",
            };

            //string url = "http://localhost:4771/api/UpdateStatus/UpdateUnitOfRentOfRentPaymentRequestDetail";
            //string url = "http://localhost:4771/api/UpdateStatus/UpdateStatusOfRentPaymentRequestDetail";
            //string url = "http://psmapi2.52mf.cn/api/UpdateStatus/UpdateStatusOfRentPaymentRequestDetail";
            //string url = "http://uat.psmapi.api.mofang.com/api/UpdateStatus/UpdateStatusOfRentPaymentRequestDetail";
            //var result = HttpRequestUtility.Post(url, JsonConvert.SerializeObject(param), "application/json");

            //MessageBox.Show(result);
        }

        private void button6_Click(object sender, EventArgs e)
        {
            ServiceReference1.PubServivePortTypeClient ncService
            = new ServiceReference1.PubServivePortTypeClient("IPubServiveSOAP11port_http", "http://192.168.27.202:655/uapws/service/pubws");//后面的参数是url

            string result = ncService.DoSendXML("MF4", "NJOP066");
            //解析XML，获取业务单元编码 
            string resultdata = GetBusinessUnitCodeInfo(result);
            if (resultdata != "")
            {
                string[] ReponseResult = resultdata.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                string BusinessUnitCode = ReponseResult[0];
                string BusinessUnitName = ReponseResult[1];
                string FatherBusinessUnitCode = ReponseResult[2];
                string FatherBusinessUnitName = ReponseResult[3];
                decimal Rate = ReponseResult[5] == "null" ? 0 : Convert.ToDecimal(ReponseResult[5]);
            }

            MessageBox.Show(result);
        }


        /// <summary>
        /// 保存XML文件
        /// </summary>
        /// <param name="xmlDoc"></param>
        /// <returns></returns>
        public static string SaveXml(XmlDocument xmlDoc)
        {
            string strPath = AppDomain.CurrentDomain.BaseDirectory + "XmlFiles";
            if (!Directory.Exists(strPath))
                Directory.CreateDirectory(strPath);

            string strFileName = strPath + "/" + Guid.NewGuid().ToString() + ".xml";
            xmlDoc.Save(strFileName);
            return strFileName;
        }

        /// <summary>
        /// 获取业务单元返回值
        /// </summary>
        /// <param name="strXml"></param>
        /// <returns></returns>
        public static string GetBusinessUnitCodeInfo(string strXml)
        {
            StringBuilder sb = new StringBuilder();
            string result = "";

            try
            {
                if (!string.IsNullOrEmpty(strXml))
                {
                    string strNewXml = strXml.Replace("<?xml version=\"1.0\" encoding=\"UTF-8\"?>", "");
                    sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
                    sb.Append("<root>");
                    sb.Append(strNewXml);
                    sb.Append("</root>");
                    XmlDocument xmlDoc = new XmlDocument();
                    xmlDoc.LoadXml(sb.ToString());

                    string strFileName = SaveXml(xmlDoc);
                    XDocument doc = XDocument.Load(strFileName);
                    foreach (XElement element in doc.Element("root").Element("result").Elements())
                    {
                        if (element.Name == "resultCode" && element.Value == "00")
                        {
                            foreach (XElement elementNode in doc.Element("root").Element("data").Elements())
                            {
                                if (elementNode.Name == "Corpcode")
                                {
                                    result += elementNode.Value + ",";
                                }
                                if (elementNode.Name == "Corpname")
                                {
                                    result += elementNode.Value + ",";
                                }
                                if (elementNode.Name == "Fathercode")
                                {
                                    result += elementNode.Value + ",";
                                }
                                if (elementNode.Name == "Fathername")
                                {
                                    result += elementNode.Value + ",";
                                }
                                if (elementNode.Name == "Procode")
                                {
                                    result += elementNode.Value + ",";
                                }
                                if (elementNode.Name == "Rate")
                                {
                                    result += elementNode.Value + ",";
                                }
                            }
                        }
                    }

                    File.Delete(strFileName);
                }
            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            return result;
        }

        private void btnImportContractInfo_Click(object sender, EventArgs e)
        {
            string strKey = this.textBox3.Text.Trim();
            string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2048;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";
            connString += "DBQ=" + strKey;
            OdbcConnection conn = new OdbcConnection(connString);
            OdbcCommand cmd = new OdbcCommand();
            cmd.Connection = conn;

            string sql = "";
            sql = "select * from [Sheet1$]";

            cmd.CommandText = sql;
            OdbcDataAdapter da = new OdbcDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds); 

            StringBuilder sb = new StringBuilder(); 
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                sb.Append(" insert into contact");
                sb.Append(" (CompanyContractNo,RoomNo,BedNo,ContractUserName,ContactAddress,ContactPhone,Remark,");
                sb.Append("  ServiceMonthPrice,ContractStartDate,ContractEndDate,CheckInDate,IDNo,ExpireMonthCount,BillAlreadyPeriod,");
                sb.Append("  CompanyContractNo,StoreID,RoomID,BedID,");
                sb.Append("  MerchantCode,ContractType,ContractBizType,ContractStatus,IsBreakContract,RentMonthPrice,DepartureDate,");
                sb.Append("  MemberCode,IDType,HotWaterInitial,ElectricityInitial,WaterInitial,Deposit,OldContractNo,");
                sb.Append("  IsValid,CreatedTime,CreatedBy,ModifiedTime,ModifiedBy)");

                sb.Append("  values (");

                sb.Append("  '" + ds.Tables[0].Rows[i]["房间号"].ToString() + "','" + ds.Tables[0].Rows[i]["床位号"].ToString() + "',");
                sb.Append("  '" + ds.Tables[0].Rows[i]["入住人"].ToString() + "','" + ds.Tables[0].Rows[i]["联系地址"].ToString() + "',");
                sb.Append("  '" + ds.Tables[0].Rows[i]["联系电话"].ToString() + "','" + ds.Tables[0].Rows[i]["备注"].ToString() + "',");
                sb.Append("  '" + ds.Tables[0].Rows[i]["服务费"].ToString() + "','" + ds.Tables[0].Rows[i]["合同开始时间"].ToString() + "',");
                sb.Append("  '" + ds.Tables[0].Rows[i]["合同结束时间"].ToString() + "','" + ds.Tables[0].Rows[i]["实际入住时间"].ToString() + "',");


                string strContractMonth = GetExpireMonthCount(ds.Tables[0].Rows[i]["合同开始时间"].ToString(), ds.Tables[0].Rows[i]["合同结束时间"].ToString());
                string strStoreId = GetStoreId(ds.Tables[0].Rows[i]["门店名称"].ToString());
                string strRoomID = GetRoomID(strStoreId,ds.Tables[0].Rows[i]["房间号"].ToString());
                string strBillAlreadyPeriod = GetBillAlreadyPeriod(ds.Tables[0].Rows[i]["合同开始时间"].ToString());

                sb.Append("  '" + ds.Tables[0].Rows[i]["身份证号"].ToString() + "','" + strContractMonth + "',"); 
                sb.Append("  '" + strBillAlreadyPeriod + "',");
                sb.Append("  '','"+ strStoreId + "','"+ strRoomID + "','',");
                sb.Append("  'U9001','0','0','2','0','0',NULL,'','1',NULL,NULL,NULL,'" + ds.Tables[0].Rows[i]["个人押金"].ToString() + "','',");
                sb.Append("  NULL,'1',");
                sb.Append("  '" + DateTime.Now + "','System','" + DateTime.Now + "','System'");

                sb.Append("  )").Append(Environment.NewLine);
            }

            this.textBox1.Text = sb.ToString();

        }


        /// <summary>
        /// 取得合同月数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private string GetExpireMonthCount(string date1,string date2)
        {
            int intMonth = 0;
            if (!string.IsNullOrEmpty(date1) && !string.IsNullOrEmpty(date2))
            {
                DateTime strDate1 = DateTime.Parse(date1);
                DateTime strDate2 = DateTime.Parse(date2);
                intMonth = (strDate2.Year - strDate1.Year) * 12 + (strDate2.Month - strDate1.Month);
            }

            return intMonth.ToString();
        }

        /// <summary>
        /// 取得已出账单期数
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private string GetBillAlreadyPeriod(string date1)
        {
            int intMonth = 0;
            if (!string.IsNullOrEmpty(date1))
            {
                DateTime strDate1 = DateTime.Parse(date1);
                DateTime strDate2 = DateTime.Now;
                intMonth = (strDate2.Year - strDate1.Year) * 12 + (strDate2.Month - strDate1.Month);
            }

            return (intMonth+1).ToString();
        }


        /// <summary>
        /// 取得门店ID
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private string GetStoreId(string name)
        { 
            string strSql = "select * from mf_product.store where StoreName='"+ name + "'";
            var result = SQLHelper.GetDataTable(strSql); 

            return result.Rows[0]["StoreID"].ToString();
        }

        /// <summary>
        /// 取得房间ID
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        private string GetRoomID(string storeId,string roomNo)
        {
            string strSql = "select * from mf_product.room where storeId='" + storeId + "' and roomNO='"+ roomNo + "'";
            var result = SQLHelper.GetDataTable(strSql);

            return result.Rows[0]["RoomId"].ToString();
        }
    }
}
